﻿--	创建数据库
	create database TsgxtDB
	on
	(
		name='TsgxtDB_date', --主数据文件逻辑名
		--主数据文件物理名称
		filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TsgxtDB.mdf',
		size = 5MB, --主数据文件大小
		maxsize=50MB, --主数据文件最大值
		filegrowth=20% --主数据文件增长率
	)
LOG ON
	(
	name='TsgxtDB_log', --事务日志文件逻辑名
		--事务日志文件物理名
	filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TsgxtDB.log.idf',
	size=2MB,
	filegrowth=10%
	)
	GO
use TsgxtDB;

--借书卡表     
if object_id('Card')is not null
begin 
drop table Card
end 
create table Card
(
CNO int not null,
Name varchar(25) not null,
ClassName varchar(50) not null
)
Go
--图书表
if object_id('Books')is not null
begin
drop table Books
end 
create table Books
(
BNo int not null,
BName varchar(255) not null,
Author varchar(255) not null,
Price money not null,
Quantity int not null
)
GO
if object_id('Borrow')is not null
begin
drop table Borrow
end 
create table Borrow
(
CNo int not null,
BNo int  not null,
RDate date not null,
BDate date not null,
)
--定义主键约束和外键约束

alter table Borrow 
add 
constraint CS_CNO_Borrow primary key (BNo,CNo)

alter table Borrow
add
constraint FK_Card_CNo foreign key(CNo) references [Card](CNo),
constraint FK_Books_BNo foreign key(BNo) references Books(BNo)


--添加数据
insert into [Card](CNo,Name,ClassName)
values(1,'张青云','C01班'),
(2,'吕国庆','C02班'),
(3,'张国强','C03班'),
(4,'狗超','C04班'),
(5,'胖虎','C05班'),
(6,'狗表','C06班'),
(7,'张宇','C07班'),
(8,'王萌萌','C08班'),
(9,'田亮','C09班'),
(10,'刘翔','C10班')


--图书表添加数据

insert into Books(BNo,BName,Author,Price,Quantity)
values--(1,'红楼梦','曹雪芹',23.40,15),
(2,'西游记','吴承恩',25.00,23),
(3,'三国演义','罗贯中',34.00,12),
(4,'水浒传','施耐庵',23.00,18),
(5,'Spring Boot 实践','阿三',34.00,6),
(6,'SSM轻量级框架应用实战','李四',23.00,7),
(7,'JAVA数据结构和算法(第二版.影印版)','王五',45.00,8),
(8,'SQL SERVER 从入门到精通(第二版)','李白',34.00,6),
(9,'网络信息与技术','张晓欢',56.00,9),
(10,'初识JAVA','夏雨',23.00,5)



--借书表添加数据

insert into Borrow(CNo,BNo,RDate,BDate)
values(1,1,'2017-10-01','2017-09-08'),
(1,2,'2017-11-08','2017-09-08'),
(6,3,'2017-12-08','2017-11-08'),
(4,4,'2017-10-08','2017-09-08'),
(1,5,'2017-12-09','2017-09-09'),
(3,7,'2018-03-17','2018-03-01'),
(4,6,'2018-02-15','2018-02-01'),
(5,3,'2017-09-08','2017-07-08'),
(7,4,'2017-09-23','2017-08-08'),
(8,8,'2017-06-08','2017-05-08')


-- 找出借书超过5本的读者
SELECT CNo,count(BNo) FROM Borrow GROUP BY CNo HAVING COUNT(BNo)>5; 

--查询借阅了"水浒传"一书的读者，输出读者姓名及所在班级
SELECT Name,ClassName FROM [Card] WHERE CNo IN (SELECT CNo FROM Borrow WHERE BNo=(SELECT BNo FROM Books  WHERE BName='水浒传'));
SELECT * FROM BORROW WHERE RDATE<GETDATE();

--查询书名包括"网络"关键词的图书
select bno,bname,author from Books where BName like '%网络%'

--查询现有图书中价格最高的图书，输出书名及作者
SELECT BNO,BNAME,AUTHOR FROM BOOKS WHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS);

--输出其借书卡号，并按卡号降序排序输出
SELECT a.CNo FROM BORROW a,Books b WHERE a.BNo=b.BNo AND b.BName='JAVA数据结构和算法(第二版.影印版)'AND 
NOT EXISTS(SELECT * FROM BORROW aa,Books bb WHERE aa.BNO=bb.BNO AND bb.BName='SQL SERVER 从入门到精通(第二版)' AND aa.CNo=a.CNo)
ORDER BY a.CNo DESC;

--． 将"C01"班同学所借图书的还期都延长一周
UPDATE Borrow SET RDate=(SELECT  DATEADD(DAY,7,GETDATE())) WHERE CNo IN (SELECT CNo FROM [Card] WHERE ClassName='C01班');

--删除当前无人借阅的图书记录
DELETE FROM Books WHERE BNo IN (SELECT BNo FROM Books WHERE BNo NOT IN (SELECT BNo FROM Borrow));

--查询当前同时借有"Spring Boot实战"和" SSM轻量级框架应用实战"两本书的读者
SELECT CNo FROM Borrow 
WHERE CNo IN (SELECT CNo FROM Borrow 
WHERE BNo=(SELECT BNo FROM Books WHERE BName='Spring Boot 实践')) 
AND BNo=(SELECT BNo FROM Books WHERE BName='SSM轻量级框架应用实战') ORDER BY CNo
